
do "E:\yungyu\lottery_baby\do\set_environment.do"

capture log close
log using "$log\2.4.merge_variables_`c(current_date)'.log" , replace
clear
set more off

use "$wdata\single_and_first_event_winners.dta", clear

drop above totabove
foreach x in pers_prize spouse_prize pers_real_prize spouse_real_prize pers_real_prize_aftertax spouse_real_prize_aftertax tot_real_prize_aftertax{
	rename `x' winyr_`x'
}

** Household ID
sort real_win_year pers_idn spouse_idn
gen houseid = _n
format houseid %12.0g

** Panel from 93 to 109
expand 17
sort houseid
by houseid: replace year = _n + 92
gen norm_year = year - win_year

** Prize record
merge 1:1 real_win_year win_year year pers_idn spouse_idn using "$wdata\individual_winners.dta"
drop if _m == 2
drop _m

** Marriage record
gen married = 0

forv i = 93(1)109{
	dis "merge marrital information of year `i'..."
	merge m:1 year pers_idn using "$public\couple\couple_`i'.dta", update noreport
	drop if _m == 2
	replace married = 1 if couple_idn~=""
	drop _m couple_idn
}

** Fertility record
rename pers_idn idn
forv i = 93(1)109{
	dis "merge fertility information of year `i'..."
	merge m:1 idn year using "$wdata\childnum_`i'.dta", update noreport
	quiet drop if _m==2
	drop _m
}

** Fertility record
forv i = 93(1)109{
	dis "merge death information of year `i'..."
	merge m:1 idn year using "$wdata\died_childnum_`i'.dta", update noreport
	quiet drop if _m==2
	drop _m
}

merge m:1 idn year using "$wdata\death_childnum.dta", update noreport

rename idn pers_idn
foreach y in prebabyboy prebabygirl babyboy babygirl totbabyboy totbabygirl  prediedbabyboy prediedbabygirl diedbabyboy diedbabygirl totdiedbabyboy totdiedbabygirl diedin0 diedin1 diedin2{
	quiet replace `y' = 0 if `y' == .
}

quiet gen prebaby = prebabyboy + prebabygirl
quiet gen baby = babyboy + babygirl
quiet gen totbaby = totbabyboy + totbabygirl
quiet gen prediedbaby = prediedbabyboy + prediedbabygirl
quiet gen diedbaby = diedbabyboy + diedbabygirl
quiet gen totdiedbaby = totdiedbabyboy + totdiedbabygirl

save "$wdata/temp.dta", replace

** Income and wealth record
foreach x in "pers" "spouse" {
    
	if ("`x'" == "spouse") rename pers_idn self_idn
	
	rename `x'_idn idn
	** Income
	forv i =93(1)109{
		dis "merge `x' income information of year `i'..."
		cap noi merge m:1 year idn using "$wdata/income_`i'.dta" , update noreport //As couple idn with some missing, cannot uniquely identify, using m:1 to merge
		cap drop if _m==2
		cap drop _m income_*
		** Earnings
		dis "merge `x' earnings information of year `i'..."
		merge m:1 year idn using "$earnings/earnings_`i'.dta" , update noreport  //As couple idn with some missing, cannot uniquely identify, using m:1 to merge
		quiet drop if _m==2
		if `i' < 104{
			quiet replace wage = wage + est_pay if year == `i'
			drop est
		}
		drop _m
		lab var busi "business income"
		lab var prof "professional income"
		lab var wage "wage income"
		lab var earnings "earnings"
	}

	** Wealth
	rename idn pers_idn
	forv i =93(1)109{
		dis "merge `x' wealth information of year `i'..."
		merge m:1 year pers_idn using "$wealth/ind_wealth_`i'.dta", update replace noreport keepusing(tot_mrkt_net_t21 rl_est_mrkt houseloan_v4 captl_tot_saving value_stk_2 bu_ast_1)
		quiet drop if _m==2
		drop _m
	}

	quiet{
		gen asset = tot_mrkt_net_t21	
		gen rlest = rl_est
		gen hloan = houseloan_v4
		gen saving = captl_tot_saving
		gen stock = value_stk_2 + bu_ast_1
		replace bu_ast_1 = 0 if bu_ast_1 < 0
		gen grossstock = value_stk_2 + bu_ast_1
		gen grossasset = rl_est_mrkt + captl_tot_saving + value_stk_2 + bu_ast_1
		gen liquidasset = grossstock + saving
	}
	drop tot_mrkt_net* rl_est_mrkt houseloan_v4 captl_tot_saving value_stk_2 bu_ast_1
	 
	foreach y in "income" "busi" "prof" "wage" "earnings" "asset" "rlest" "hloan" "saving" "stock" "grossstock" "grossasset" "liquidasset"{
		rename `y' `x'_`y'
		dis "set `y' missing to zero"
		recode `x'_`y' . = 0
	}
	rename pers_idn `x'_idn
	
	if ("`x'" == "spouse") rename self_idn pers_idn 
}

** Adjusted for CPI
cap drop _merge
merge m:1 year using "$wdata/cpi.dta", noreport
drop if _m == 2
drop cpi_sy _merge
foreach x in "pers" "spouse" {
	foreach y in "income" "busi" "prof" "wage" "earnings"{
		dis "adjust `x's' `y' by cpi..."
		replace `x'_`y' = `x'_`y'/cpi*100
	}
}

** Residence
rename year tax_year
rename win_year year

foreach x in "pers" "spouse" {
	if ("`x'" == "spouse") rename pers_idn self_idn  
	rename `x'_idn pers_idn
	forv i = 96(1)101{
		dis "merge `x's' resident information of year `i'..."
		merge m:1 year pers_idn using "$public/residence/resident_`i'.dta", update noreport keepusing(hsn_cd town_cd)
		quiet drop if _m == 2
		drop _m
	}
	rename hsn_cd `x'_hsn
	rename town_cd `x'_town
	rename pers_idn `x'_idn
	if ("`x'" == "spouse") rename self_idn pers_idn   
}

rename year win_year
rename tax_year year

quiet gen houhld_resid = pers_hsn

drop if houhld_resid == ""
encode houhld_resid, gen(houhld_resid_cd)

** Adjust income/earning/wealth/prize to thousand
foreach x of varlist *pers_prize* *spouse_prize* *pers_real_prize* *spouse_real_prize* pers_income-pers_earnings spouse_income-spouse_earnings{
	replace `x' = `x' / 1000
}
foreach x of varlist pers_asset-pers_liquidasset spouse_asset-spouse_liquidasset{
	replace `x' = `x' * 1000
}

gen resident_tpe = houhld_resid == "A"
gen resident_munici = houhld_resid == "B" | houhld_resid == "D" | houhld_resid == "E" | houhld_resid == "F" | houhld_resid == "H" | houhld_resid == "L" | houhld_resid == "R" | houhld_resid == "S"
gen resident_other = resident_tpe == 0 & resident_munici == 0
gen resident_sixcities = resident_tpe == 1 | resident_munici == 1 

xtset houseid norm_year

gen age = year-pers_born_year
gen age_sq = age^2

compress

lab var current "Current Winner (Lottery Year: 96-101)"
lab var future "Current Winner (Lottery Year: 103-108)"
lab var pers_idn "Winner ID"
lab var spouse_idn "Spouse ID"
lab var houseid "Household ID"
lab var year "Observed Year"
lab var win_year "(Pseudo) Lottery Year"
lab var real_win_year "Actual Lottery Year"
lab var norm_year "Year from Lottery"

lab var pers_gender "Winner's gender"
lab var pers_born_year "Winner's birth year"
lab var pers_death_year "Winner's death year"
lab var spouse_gender "Spouse's gender"
lab var spouse_born_year "Spouse's birth year"
lab var spouse_death_year "Spouse's death year"

lab var winyr_pers_prize "Winner's Prize Amount in Lottery Year (raw dollar)"
lab var winyr_spouse_prize "Spouse's Prize Amount in Lottery Year (raw dollar)"
lab var winyr_pers_real_prize "Winner's Prize Amount in Lottery Year (real dollar)"
lab var winyr_spouse_real_prize "Spouse's Prize Amount in Lottery Year (real dollar)"
lab var winyr_pers_real_prize_aftertax "Winner's Prize Amount in Lottery Year (real dollar, after tax)"
lab var winyr_spouse_real_prize_aftertax "Spouse's Prize Amount in Lottery Year (real dollar, after tax)"
lab var winyr_tot_real_prize_aftertax "Total (personal + spouse) Prize Amount in Lottery Year (real dollar, after tax)"

lab var pers_prize "Winner's Prize Amount in Observed Year (raw dollar)"
lab var spouse_prize "Spouse's Prize Amount in Observed Year (raw dollar)"
lab var pers_real_prize "Winner's Prize Amount in Observed Year (real dollar)"
lab var spouse_real_prize "Spouse's Prize Amount in Observed Year (real dollar)"
lab var pers_real_prize_aftertax "Winner's Prize Amount in Observed Year (real dollar, after tax)"
lab var spouse_real_prize_aftertax "Spouse's Prize Amount in Observed Year (real dollar, after tax)"

lab var win_age "Winner's Age in Lottery Year"
lab var age "Winner's Age in Observed Year"
lab var age_sq "Winner's Age Squared in Observed Year"

lab var prebaby "Culmulative baby in last year"
lab var prebabyboy "Culmulative baby boy in last year"
lab var prebabygirl "Culmulative baby girl in last year"
lab var baby "Number of baby given birth in observed year"
lab var babyboy "Number of baby boy given birth in observed year"
lab var babygirl "Number of baby girl given birth in observed year"
lab var totbaby "Culmulative number of baby"
lab var totbabyboy "Culmulative number of baby boy"
lab var totbabygirl "Culmulative number of baby girl"

lab var pers_income "Total income (exclude lottery income)"
lab var spouse_income "Total income (exclude lottery income)"

lab var married "Married"

order current future houseid pers_idn spouse_idn win_year real_win_year year norm_year pers_gender pers_born_year pers_death_year win_age age age_sq spouse_gender spouse_born_year spouse_death_year winyr* pers_prize pers_real_prize pers_real_prize_aftertax spouse_prize spouse_real_prize spouse_real_prize_aftertax prebabyboy prebabygirl babyboy babygirl totbabyboy totbabygirl prebaby baby totbaby prediedbabyboy prediedbabygirl diedbabyboy diedbabygirl totdiedbabyboy totdiedbabygirl prediedbaby diedbaby totdiedbaby diedin* married pers_income pers_busi pers_prof pers_wage pers_earnings pers_asset pers_rlest pers_hloan pers_saving pers_stock pers_grossstock pers_grossasset pers_liquidasset spouse_income spouse_busi spouse_prof spouse_wage spouse_earnings spouse_asset spouse_rlest spouse_hloan spouse_saving spouse_stock spouse_grossstock spouse_grossasset spouse_liquidasset pers_hsn pers_town spouse_hsn spouse_town houhld_resid houhld_resid_cd resident_tpe resident_munici resident_other resident_sixcities cpi

keep current future houseid pers_idn spouse_idn win_year real_win_year year norm_year pers_gender pers_born_year pers_death_year win_age age age_sq spouse_gender spouse_born_year spouse_death_year winyr* pers_prize pers_real_prize pers_real_prize_aftertax spouse_prize spouse_real_prize spouse_real_prize_aftertax prebabyboy prebabygirl babyboy babygirl totbabyboy totbabygirl prebaby baby totbaby prediedbabyboy prediedbabygirl diedbabyboy diedbabygirl totdiedbabyboy totdiedbabygirl prediedbaby diedbaby totdiedbaby diedin* married pers_income pers_busi pers_prof pers_wage pers_earnings pers_asset pers_rlest pers_hloan pers_saving pers_stock pers_grossstock pers_grossasset pers_liquidasset spouse_income spouse_busi spouse_prof spouse_wage spouse_earnings spouse_asset spouse_rlest spouse_hloan spouse_saving spouse_stock spouse_grossstock spouse_grossasset spouse_liquidasset pers_hsn pers_town spouse_hsn spouse_town houhld_resid houhld_resid_cd resident_tpe resident_munici resident_other resident_sixcities cpi

save "$wdata\project_lottery_fertility_full_panel_93109.dta" , replace

log close
